import { App, Config, Inject, Provide } from '@midwayjs/core';
import { BaseService } from '@cool-midway/core';
import { InjectDataSource, InjectEntityModel } from '@midwayjs/typeorm';
import { DataSource, Repository, In } from 'typeorm';
import { UserInfoEntity } from '../entity/info';
import { UserCertEntity } from '../entity/cert.entity';
import { UserContactEntity } from '../entity/contact.entity';
import * as oracledb from 'oracledb';
import { IMidwayApplication } from '@midwayjs/core';
import { ILogger } from '@midwayjs/logger';
import { OracleService } from '../../../comm/service/oracle.service';

@Provide()
export class UserSyncDataService extends BaseService {
  @InjectEntityModel(UserInfoEntity)
  userInfoEntity: Repository<UserInfoEntity>;

  @InjectEntityModel(UserCertEntity)
  userCertEntity: Repository<UserCertEntity>;

  @InjectEntityModel(UserContactEntity)
  userContactEntity: Repository<UserContactEntity>;

  @InjectDataSource()
  defaultDataSource: DataSource;

  @App()
  app: IMidwayApplication;

  @Inject()
  logger: ILogger;

  @Inject()
  oracleService: OracleService;

  private async executeOracleProcedure(
    connection: oracledb.Connection,
    procedureName: string,
    bindParams: oracledb.BindParameters,
    taskId: string,
    logPrefix: string
  ): Promise<any[]> {
    this.logger.info(`[${taskId}] ${logPrefix} Calling SP ${procedureName}...`);
    const execOptions: oracledb.ExecuteOptions = {
        outFormat: oracledb.OUT_FORMAT_OBJECT,
        autoCommit: false
    };
    try {
        const result = await connection.execute<{ dataset: oracledb.ResultSet<any> }>(
            `BEGIN ${procedureName}(:dataset, :yxc_b2b_clients); END;`,
            bindParams,
            execOptions
        );

        this.logger.debug(`[${taskId}] ${logPrefix} SP ${procedureName} raw result outBinds:`, result.outBinds);

        if (result && result.outBinds && result.outBinds.dataset) {
            const resultSet = result.outBinds.dataset;
            if (resultSet && typeof resultSet.getRows === 'function') {
                const rows = await resultSet.getRows();
                await resultSet.close();
                this.logger.info(`[${taskId}] ${logPrefix} Fetched ${rows.length} rows from Oracle SP ${procedureName}.`);
                return rows;
            } else {
                this.logger.warn(`[${taskId}] ${logPrefix} SP ${procedureName} result did not contain a valid ResultSet in outBinds.dataset. Value:`, resultSet);
                return [];
            }
        } else {
            this.logger.warn(`[${taskId}] ${logPrefix} SP ${procedureName} did not return expected outBinds or outBinds.dataset.`);
            return [];
        }
    } catch (spError) {
        this.logger.error(`[${taskId}] ${logPrefix} Error executing SP ${procedureName}:`, spError);
        return [];
    }
  }

  async syncAllUserData(taskId: string = 'manualSyncAll') {
    this.logger.info(`[${taskId}] 开始执行用户数据同步任务 (Info, Cert, Contact)...`);
    let totalClientsToSync = 0;
    let processedClients = 0;
    let infoUpdated = 0, infoInserted = 0;
    let certUpserted = 0;
    let contactUpserted = 0;

    const currentOracleDataSource = this.oracleService.getDataSource();
    if (!currentOracleDataSource || !currentOracleDataSource.isInitialized) {
        this.logger.error(`[${taskId}] Oracle 数据源 (via OracleService) 不可用或未初始化，任务中止。`);
        return;
    }

    let connection: oracledb.Connection | undefined;
    try {
        this.logger.info(`[${taskId}] 获取 Oracle 连接 (从 DataSource 连接池)...`);
        const driver = currentOracleDataSource.driver as any;
        const pool = driver.pool || driver.master;

        if (!pool || typeof pool.getConnection !== 'function') {
            this.logger.error(`[${taskId}] 无法从 Oracle DataSource driver 获取连接池. Driver keys: ${Object.keys(driver).join(', ')}`);
            throw new Error('Could not obtain Oracle connection pool from TypeORM DataSource driver.');
        }

        connection = await pool.getConnection();
        this.logger.info(`[${taskId}] Oracle 连接获取成功.`);

        this.logger.info(`[${taskId}] 查询 YXC_B2B_INTERFACELIST 获取待同步客户ID...`);
        const clientsQuery = `SELECT DISTINCT RFID FROM YXC_B2B_INTERFACELIST WHERE INTERFACETYPE = 'CLIENTS' AND RFID IS NOT NULL`;
        const clientResult = await connection.execute<{ RFID: string }>(clientsQuery, [], { outFormat: oracledb.OUT_FORMAT_OBJECT });

        const clientIdsToSync = clientResult.rows?.map(row => row.RFID) || [];
        totalClientsToSync = clientIdsToSync.length;
        this.logger.info(`[${taskId}] 从 YXC_B2B_INTERFACELIST 获取到 ${totalClientsToSync} 个待同步的客户ID.`);

        if (totalClientsToSync === 0) {
            this.logger.info(`[${taskId}] 没有需要同步的客户数据.`);
            return;
        }

        this.logger.info(`[${taskId}] 获取本地 UserInfo 映射...`);
        const localUsers = await this.userInfoEntity.find({ select: ['id', 'erpBusinessId'] });
        const localUserMap = new Map(localUsers.map(u => [u.erpBusinessId, u.id]));
        this.logger.info(`[${taskId}] 获取到 ${localUserMap.size} 个本地 UserInfo 记录.`);

        for (const clientId of clientIdsToSync) {
            processedClients++;
            const logPrefix = `Client ${clientId} (${processedClients}/${totalClientsToSync}):`;
            this.logger.info(`[${taskId}] ${logPrefix} 开始处理...`);
            let userInfoProcessedSuccessfully = false;

            try {
                const userInfoParams: oracledb.BindParameters = {
                    dataset: { type: oracledb.CURSOR, dir: oracledb.BIND_OUT },
                    yxc_b2b_clients: { type: oracledb.STRING, dir: oracledb.BIND_IN, val: clientId }
                };
                const oracleUsers = await this.executeOracleProcedure(connection, 'YXC_B2B_Login', userInfoParams, taskId, `${logPrefix} UserInfo`);

                if (oracleUsers && oracleUsers.length > 0) {
                    const oracleUser = oracleUsers[0];
                    const existingLocalUserId = localUserMap.get(clientId);
                    
                    // 数据清理：trim 去掉前后空格
                    let phoneValue = oracleUser.PHONE;
                    if (phoneValue && typeof phoneValue === 'string') {
                        phoneValue = phoneValue.trim();
                        this.logger.debug(`[${taskId}] ${logPrefix} Oracle PHONE 字段已 trim: '${phoneValue}'`);
                    }
                    
                    const userData:any = {
                        erpBusinessId: clientId,
                        username: oracleUser.USERNAME,
                        nickName: oracleUser.USERNAME,
                        realName: oracleUser.REALNAME,
                        phone: phoneValue,
                        address: oracleUser.ADDRESS,
                        telephone: oracleUser.TELEPHONE,
                        areaName: oracleUser.AREANAME,
                        levelId: oracleUser.LEVELID,
                        status: oracleUser.STATUS === 1 ? 1 : 0,
                        isPres: oracleUser.ISPRES,
                    };

                    try {
                        if (existingLocalUserId) {
                            await this.userInfoEntity.update(existingLocalUserId, userData);
                            infoUpdated++;
                            userInfoProcessedSuccessfully = true;
                            this.logger.debug(`[${taskId}] ${logPrefix} UserInfo 更新 (ID: ${existingLocalUserId}).`);
                        } else {
                            // 检查是否存在相同 phone 的用户 (避免唯一索引冲突)
                            if (phoneValue) {
                                const existingUserWithPhone = await this.userInfoEntity.findOne({ where: { phone: phoneValue } });
                                if (existingUserWithPhone) {
                                    this.logger.warn(`[${taskId}] ${logPrefix} 已存在相同 phone 的用户 (ID: ${existingUserWithPhone.id}), 将清空当前记录的 phone 字段`);
                                    userData.phone = null;
                                }
                            }
                            
                            userData.password = '23ea63252b8d8f43d75101a4f35841af';
                            const newUser: any = this.userInfoEntity.create(userData);
                            await this.userInfoEntity.save(newUser);
                            localUserMap.set(clientId, newUser.id);
                            infoInserted++;
                            userInfoProcessedSuccessfully = true;
                            this.logger.debug(`[${taskId}] ${logPrefix} UserInfo 新增 (New ID: ${newUser.id}).`);
                        }
                    } catch (userInfoError) {
                        this.logger.error(`[${taskId}] ${logPrefix} UserInfo 处理失败:`, userInfoError);
                        this.logger.error(`[${taskId}] ${logPrefix} 问题数据:`, JSON.stringify(userData, null, 2));
                        userInfoProcessedSuccessfully = false;
                    }
                } else {
                    this.logger.warn(`[${taskId}] ${logPrefix} SP YXC_B2B_Login 未返回用户数据. 将跳过此用户的 Cert 和 Contact 同步.`);
                }

                if (userInfoProcessedSuccessfully) {
                    const userCertParams: oracledb.BindParameters = {
                        dataset: { type: oracledb.CURSOR, dir: oracledb.BIND_OUT },
                        yxc_b2b_clients: { type: oracledb.STRING, dir: oracledb.BIND_IN, val: clientId }
                    };
                    const oracleCerts = await this.executeOracleProcedure(connection, 'YXC_B2B_USER_CERT', userCertParams, taskId, `${logPrefix} UserCert`);

                    if (oracleCerts && oracleCerts.length > 0) {
                        this.logger.debug(`[${taskId}] ${logPrefix} Deleting existing certs for erpBusinessId: ${clientId}`);
                        await this.userCertEntity.delete({ erpBusinessId: clientId });

                        const certsToSave = oracleCerts.map(cert => this.userCertEntity.create({
                            erpBusinessId: clientId,
                            xuHao: cert.XUH,
                            certName: cert.CERTNAME,
                            certCode: cert.CERTCODE,
                            valDate: cert.VALDATE,
                            conScope: cert.CONSCOPE,
                            businessCont: cert.BUSINESSCONT
                        }));

                        if (certsToSave.length > 0) {
                           await this.userCertEntity.save(certsToSave, { chunk: 50 });
                           certUpserted += certsToSave.length;
                           this.logger.debug(`[${taskId}] ${logPrefix} UserCert 保存了 ${certsToSave.length} 条记录.`);
                        }
                    } else {
                         this.logger.info(`[${taskId}] ${logPrefix} SP YXC_B2B_USER_CERT 未返回证照数据.`);
                         await this.userCertEntity.delete({ erpBusinessId: clientId });
                         this.logger.debug(`[${taskId}] ${logPrefix} Deleted existing certs as SP returned no data.`);
                    }

                    const userContactParams: oracledb.BindParameters = {
                        dataset: { type: oracledb.CURSOR, dir: oracledb.BIND_OUT },
                        yxc_b2b_clients: { type: oracledb.STRING, dir: oracledb.BIND_IN, val: clientId }
                    };
                    const oracleContacts = await this.executeOracleProcedure(connection, 'YXC_B2B_USER_CONTACT', userContactParams, taskId, `${logPrefix} UserContact`);

                    if (oracleContacts && oracleContacts.length > 0) {
                        this.logger.debug(`[${taskId}] ${logPrefix} Deleting existing contacts for erpBusinessId: ${clientId}`);
                        await this.userContactEntity.delete({ erpBusinessId: clientId });

                        const contactsToSave = oracleContacts.map(contact => this.userContactEntity.create({
                            erpBusinessId: clientId,
                            contactId: contact.CONTACTID,
                            contact: contact.CONTACT,
                            idCard: contact.IDCARD,
                            phone: contact.PHONE,
                            consCertDate: contact.CONSCERTDATE,
                            consMandate: contact.CONSMANDATE,
                            businessCont: contact.BUSINESSCONT
                        }));

                        if (contactsToSave.length > 0) {
                            await this.userContactEntity.save(contactsToSave, { chunk: 50 });
                            contactUpserted += contactsToSave.length;
                            this.logger.debug(`[${taskId}] ${logPrefix} UserContact 保存了 ${contactsToSave.length} 条记录.`);
                        }
                    } else {
                        this.logger.info(`[${taskId}] ${logPrefix} SP YXC_B2B_USER_CONTACT 未返回联系人数据.`);
                         await this.userContactEntity.delete({ erpBusinessId: clientId });
                         this.logger.debug(`[${taskId}] ${logPrefix} Deleted existing contacts as SP returned no data.`);
                    }
                }

                 this.logger.info(`[${taskId}] ${logPrefix} 处理完成.`);

                // 同步完成后，删除 YXC_B2B_INTERFACELIST 中对应的记录
                const deleteSql = `DELETE FROM YXC_B2B_INTERFACELIST WHERE RFID = :clientId AND INTERFACETYPE = 'CLIENTS'`;
                await connection.execute(deleteSql, { clientId }, { autoCommit: true });
                this.logger.info(`[${taskId}] ${logPrefix} 已从 YXC_B2B_INTERFACELIST 删除同步完成的记录.`);

            } catch (clientError) {
                 this.logger.error(`[${taskId}] ${logPrefix} 处理失败:`, clientError);
            }
        }

        this.logger.info(`[${taskId}] 用户数据同步任务完成.`);
        this.logger.info(`    总计待同步客户: ${totalClientsToSync}`);
        this.logger.info(`    成功处理客户: ${processedClients}`);
        this.logger.info(`    UserInfo: 更新 ${infoUpdated} 条, 新增 ${infoInserted} 条`);
        this.logger.info(`    UserCert: Upsert ${certUpserted} 条 (按先删后插计算)`);
        this.logger.info(`    UserContact: Upsert ${contactUpserted} 条 (按先删后插计算)`);

    } catch (error) {
        this.logger.error(`[${taskId}] 用户数据同步任务执行期间发生严重错误:`, error);
    } finally {
        if (connection) {
            this.logger.info(`[${taskId}] 释放 Oracle 连接回连接池...`);
            try {
                await connection.close();
                this.logger.info(`[${taskId}] Oracle 连接已释放.`);
            } catch (releaseError) {
                this.logger.error(`[${taskId}] 释放 Oracle 连接时出错:`, releaseError);
            }
        }
    }
  }

  async syncUserData(taskId: string = 'manualSyncUserInfoOnly') {
     this.logger.warn(`[${taskId}] 调用了已废弃的 syncUserData 方法，仅同步 UserInfo。请考虑使用 syncAllUserData。`);
     this.logger.info(`[${taskId}] 旧的 syncUserData 方法执行完毕 (无实际操作).`);
  }
}